﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Linq.Expressions;
using Sio.Mdm.Entities;
using Sio.Mdm.BL.Base;
using Sio.Mdm.Types;

namespace Sio.Mdm.BL
{
    /// <summary>
    /// Implementation of base buisness logic class
    /// </summary>
    public abstract class BaseBL : Sio.Mdm.Server.BaseService
    {
        #region Construction
        public BaseBL()
            : this(null)
        {
        }

        public BaseBL(string connectionString)
        {
            this._connectionString = connectionString;
        }
        #endregion


        #region Properties

        #region ConnectionName
        private string _connectionString = null;
        protected virtual string ConnectionName
        {
            get
            {
                if (String.IsNullOrEmpty(_connectionString))
                {
                    throw new Exception("You must provide connection connection string or connection name from web.config!");
                }

                return _connectionString;
            }
        }

        #endregion

        #endregion

        #region Methods

        #region Public

        #region Transactions
        public DbTransaction BeginTransaction()
        {
            DbConnection conneciton = CreateConnection();
            conneciton.Open();
            return conneciton.BeginTransaction();
        }
        public bool CommitTransaction(DbTransaction transaction)
        {
            DbConnection connection = transaction.Connection;

            transaction.Commit();

            if (connection != null)
            {
                connection.Close();
                connection.Dispose();
            }

            return true;
        }
        public bool RollbackTransaction(DbTransaction transaction)
        {
            DbConnection connection = transaction.Connection;

            transaction.Rollback();

            if (connection != null)
            {
                connection.Close();
                connection.Dispose();
            }

            return true;
        }
        #endregion

        #region Select


        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filter"></param>
        /// <param name="session"></param>
        /// <param name="deletedRecords">Should we select active or deleted records?</param>
        /// <returns></returns>
        public EntityCollection<T> MakeSelect<T>(ISioFilter<T> filter, Session session, bool deletedRecords) where T : class, IEntity
        {
            var sql = SqlOperations.BuildSelectCommand<T>(SelectType.Data, filter, typeof(T), deletedRecords, session);
            EntityCollection<T> result = new EntityCollection<T>();

            using (DbConnection connection = CreateConnection())
            {
                var command = connection.CreateCommand();

                command.CommandText = sql.SqlQuery;
                command.CommandType = CommandType.Text;

                foreach (var item in sql.SqlParams)
                {
                    AddParameter(command, item.Key, item.Value);
                }

                connection.Open();
                LogCommand(sql);

                DbDataReader reader = command.ExecuteReader();
                List<string> readerColumns = new List<string>();
                DataTable schema = reader.GetSchemaTable();

                foreach (DataRow row in schema.Rows)
                {
                    readerColumns.Add(row["ColumnName"].ToString());
                }

                while (reader.Read())
                {
                    IEntity entity = EntityFactory.CreateInstance(typeof(T));

                    EntityFactory.EntityInfo info = EntityFactory.GetEntityInfo(typeof(T));
                    List<string> fields = info.GetSelectableFields();

                    foreach (string field in fields)
                    {
                        string key = info.FieldMapping.ContainsKey(field.ToLower()) ?
                            info.FieldMapping[field.ToLower()] : field;

                        string schemaField = field.Replace("\"", "");

                        if (readerColumns.Contains(schemaField))
                        {
                            entity.SetField(key, reader[schemaField]);
                        }
                    }

                    entity.SetState(EntityState.Unchanged);
                    result.Add(entity);
                }

                connection.Close();
            }
            return result;

        }


        public int MakeCount<T>(ISioFilter<T> filter, Session session, bool deletedRecords) where T : class, IEntity
        {
            var sql = SqlOperations.BuildSelectCommand<T>(SelectType.Data, filter, typeof(T), deletedRecords, session);
            sql.SqlQuery = String.Format("select count(*) from ({0})", sql.SqlQuery);
            return GetValue<int>(sql);
        }

        #endregion

        #region Insert
        public bool MakeInsert<T>(IEntity entity, Session session) where T : IEntity
        {
            return MakeInsert(entity, typeof(T), session);
        }

        protected bool MakeInsert(IEntity entity, Type entityType, Session session)
        {
            var trans = BeginTransaction();
            try
            {
                var result = MakeInsert(entity, entityType, trans, session);
                CommitTransaction(trans);
                return result;
            }
            catch //(Exception ex)
            {
                RollbackTransaction(trans);
                //throw ex;
                throw;
            }
        }

        protected bool MakeInsert(IEntity entity, Type entityType, DbTransaction transaction, Session session)
        {
            var cmd = SqlOperations.BuildInsertCommand(entity, entityType, GetNextId, session);
            var insertCount = ExecuteNonQuery(cmd, transaction);

            if (insertCount > 0)
            {
                entity.SetState(EntityState.Unchanged);
                return true;
            }
            return false;
        }
        #endregion

        #region Delete

        public bool MakeDelete<T>(IEntity entity, Session session) where T : IEntity
        {
            return MakeDelete(entity, typeof(T), session);
        }


        public bool MakeDelete(IEntity entity, Type entityType, Session session)
        {
            var trans = BeginTransaction();
            try
            {
                var result = MakeDelete(entity, entityType, trans, session);
                CommitTransaction(trans);
                return result;
            }
            catch
            {
                RollbackTransaction(trans);
                return false;
            }
        }

        /// <summary>
        /// Check if entity is ready for delete from database or we should just change status field.
        /// </summary>
        /// <param name="entity"></param>
        /// <returns>It returns true if we should delete entity, and false if we should just change status field.</returns>
        private bool CheckForDelete(IEntity entity, Type entityType)
        {
            EntityFactory.EntityInfo info = EntityFactory.GetEntityInfo(entityType);
            return (info.DeletedStatusField == null);
        }

        /// <summary>
        /// Set status field on entity into deleted state
        /// </summary>
        /// <param name="entity"></param>
        private void SetDeletedStatusField(IEntity entity, Type entityType)
        {
            EntityFactory.EntityInfo info = EntityFactory.GetEntityInfo(entityType);
            entity.SetState(EntityState.Changed);
            entity.SetField(info.DeletedStatusField.Name, 1);
        }

        public bool MakeDelete(IEntity entity, Type entityType, DbTransaction transaction, Session session)
        {
            if (CheckForDelete(entity, entityType))
            {
                var cmd = SqlOperations.BuildDeleteCommand(entity, entityType, session);
                if (ExecuteNonQuery(cmd, transaction) > 0)
                {
                    entity.SetState(EntityState.Deleted);
                    return true;
                }
            }
            else
            {
                SetDeletedStatusField(entity, entityType);
                var cmd = SqlOperations.BuildDeleteByStatusCommand(entity, entityType, session);
                if (ExecuteNonQuery(cmd, transaction) > 0)
                {
                    entity.SetState(EntityState.Deleted); //ali res na deleted postavim?!
                    return true;
                }
            }


            return false;
        }
        #endregion

        #region Update
        public bool MakeUpdate<T>(IEntity entity, Session session)
        {
            return MakeUpdate(entity, typeof(T), session);
        }

        public bool MakeUpdate(IEntity entity, Type entityType, Session session)
        {

            var trans = BeginTransaction();
            try
            {
                var result = MakeUpdate(entity, entityType, trans, session);
                CommitTransaction(trans);
                return result;
            }
            catch
            {
                RollbackTransaction(trans);
                return false;
            }
        }

        public bool MakeUpdate(IEntity entity, Type entityType, DbTransaction transaction, Session session)
        {
            var cmd = SqlOperations.BuildUpdateCommand(entity, entityType, session);
            if (ExecuteNonQuery(cmd, transaction) > 0)
            {
                entity.SetState(EntityState.Unchanged);
                return true;
            }
            return false;
        }

        public bool MakeUpdate(EntityCollection collection, Session session)
        {
            bool result = true;
            DbTransaction trans = BeginTransaction();

            try
            {
                for (int i = 0; i < collection.Count; i++)
                {
                    IEntity entity = collection[i] as IEntity;

                    switch (entity.EntityState)
                    {
                        case EntityState.Changed:
                            result &= MakeUpdate(entity, collection.EntityType, trans, session);
                            break;

                        case EntityState.Created:
                            result &= MakeInsert(entity, collection.EntityType, trans, session);
                            break;

                        case EntityState.Deleted:
                            result &= MakeDelete(entity, collection.EntityType, trans, session);
                            break;
                    }
                }

                if (result)
                {
                    CommitTransaction(trans);
                }


            }
            finally
            {
                if (trans != null && !result)
                {
                    RollbackTransaction(trans);
                }
            }

            return result;
        }

        #endregion

        #region ExecuteNonQuery

        public int ExecuteNonQuery(IEnumerable<QueryCommand> commands)
        {
            var trans = BeginTransaction();
            try
            {
                var result = ExecuteNonQuery(commands, trans);
                CommitTransaction(trans);
                return result;
            }
            catch
            {
                RollbackTransaction(trans);
                return -1;
            }

        }

        public int ExecuteNonQuery(IEnumerable<QueryCommand> commands, DbTransaction transaction)
        {
            int result = 0;
            foreach (var cmd in commands)
            {
                using (DbCommand command = CreateCommand())
                {
                    command.Transaction = transaction;
                    command.Connection = transaction.Connection;
                    command.CommandText = cmd.SqlQuery;
                    foreach (var par in cmd.SqlParams)
                        AddParameter(command, par.Key, par.Value);
                    LogCommand(cmd);

                    result = command.ExecuteNonQuery();
                }
            }
            return result;
        }
        #endregion

        #region GetNextId
        /// <summary>
        /// Get next id to be used for insert in table
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public int GetNextId(string generatorName)
        {
            long result = GetValue<long>(new QueryCommand(String.Format("SELECT GEN_ID( {0}, 1 ) FROM RDB$DATABASE", generatorName)));
            return unchecked((int)result);
        }

        private T GetValue<T>(QueryCommand cmd)
        {
            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                using (DbCommand command = CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandText = cmd.SqlQuery;

                    foreach (var par in cmd.SqlParams)
                        AddParameter(command, par.Key, par.Value);

                    LogCommand(cmd);
                    var value = command.ExecuteScalar();
                    return (T)value;
                }

            }
        }


        private static void LogCommand(QueryCommand command)
        {
            Sio.Mdm.Utils.Logger.WriteDebug(string.Format("SQL: {0}", command.SqlQuery));

            foreach (var p in command.SqlParams)
            {
                if (p.Value == null || p.Value == DBNull.Value)
                {
                    Sio.Mdm.Utils.Logger.WriteDebug("SQL: -- {0} = NULL", p.Key);
                }
                else
                {
                    Sio.Mdm.Utils.Logger.WriteDebug("SQL: -- {0} = [{1}]", p.Key, p.Value);
                }
            }

        }
        #endregion

        #endregion

        #region Protected

        #region GetConnectionString
        protected string GetConnectionString()
        {
            string connString = ConnectionName;
            ConnectionStringSettings connSettings =
                ConfigurationManager.ConnectionStrings[ConnectionName];

            if (connString != null)
            {
                connString = connSettings.ConnectionString;
            }

            return connString;
        }
        #endregion

        #region Abstract
        protected abstract DbConnection CreateConnection();
        protected abstract DbCommand CreateCommand();
        protected abstract void AddParameter(DbCommand command, string paramName, object paramValue);
        #endregion

        #endregion

        #endregion

    }
}
